package sql

import (
	"WebServer/utools"
	"database/sql"
	"encoding/json"
	"errors"
	"fmt"
	"github.com/gofiber/fiber/v2/utils"
)

func VerifyUserSql(userid, token string) (int, error) {
	db, err := utools.GetDB()
	if err != nil {
		return -1, err
	} else {
		count := 0
		err := db.QueryRow(VerifyUser, userid, token).Scan(&count)
		return count, err
	}
}

type Article struct {
	Id      string         `json:"id"`       //商品id 如果存在id则为更新 不存在则为创建
	Name    string         `json:"name"`     //商品名
	Season  int            `json:"season"`   //商品季节
	Note    string         `json:"note"`     //商品详情说明
	Img     string         `json:"img"`      //商品预览图
	Class   []ArticleClass `json:"class"`    //商品选项类列表
	BarCode string         `json:"bar_code"` //商品条形码
}
type ArticleClass struct {
	Name   string        `json:"name"`   //选项类名
	Note   string        `json:"note"`   //选项说明
	Detail []ClassDetail `json:"detail"` //选项列表
}
type ClassDetail struct {
	Name  string  `json:"name"`  //选项名
	Img   string  `json:"img"`   //选项预览图
	Price float32 `json:"price"` //选项价格
}

func AddArticleSql(data []byte, userid string) error {
	res := new(Article)
	err := json.Unmarshal(data, res)
	var createClass = func(class []ArticleClass, tx *sql.Tx, articleId string) error {
		if len(res.Class) > 0 {
			for i := 0; i < len(res.Class); i++ {
				itemClass := res.Class[i]
				classId := utils.UUID()
				_, err = tx.Exec(AddArticleClass, classId, articleId, itemClass.Name, itemClass.Note)
				if err != nil {
					tx.Rollback() //分类创建失败回滚
					return err
				} else {
					if len(itemClass.Detail) > 0 {
						for i := 0; i < len(itemClass.Detail); i++ {
							detailItem := itemClass.Detail[i]
							detailId := utils.UUID()
							_, err = tx.Exec(AddClassDetail, detailId, classId, detailItem.Name, detailItem.Img, detailItem.Price)
							if err != nil {
								tx.Rollback() //分类项创建失败回滚
								return err
							}
						}
					}
				}
			}
		}
		return nil
	}
	if err != nil {
		return errors.New("request body type is not correct")
	} else {
		db, err := utools.GetDB()
		if err != nil {
			return errors.New("db connect fail")
		} else {
			tx, err := db.Begin()
			if err != nil {
				if tx != nil {
					tx.Rollback() //事务创建失败回滚
				}
				return err
			}
			if len(res.Name) <= 0 {
				return errors.New("name must not null")
			}
			if res.Season > 5 || res.Season < 1 {
				return errors.New("season must between 1 and 5")
			}
			if len(res.Id) == 0 {
				articleId := utils.UUID()
				_, err = tx.Exec(AddUserArticle, articleId, userid, res.Name, res.Season, res.Note, res.Img, res.BarCode)
				if err != nil {
					tx.Rollback() //商品创建失败回滚
					return err
				}
				err = createClass(res.Class, tx, articleId)
				if err != nil {
					return err
				}
			} else {
				_, err := tx.Exec(UpdataUserHouse, res.Name, res.Img, res.Season, res.Note, res.BarCode, res.Id)
				_, err = tx.Exec(DropHouseClass, res.Id)
				_, err = tx.Exec(DropInv, res.Id)
				if err != nil {
					tx.Rollback()
					return nil
				}
				err = createClass(res.Class, tx, res.Id)
				if err != nil {
					return nil
				}
			}
			err = tx.Commit()
			if err != nil {
				tx.Rollback() //提交失败回滚
				return err
			}
		}
		return nil
	}
}

type ListRes struct {
	Total int           `json:"total"` //列表总量
	Size  int           `json:"size"`  //请求列表量
	Page  int           `json:"page"`  //请求页码
	List  []ListResItem `json:"list"`  //列表数组
}

type ListResItem struct {
	Id         string  `json:"id"`         //商品id
	Name       string  `json:"name"`       //商品名称
	Season     int     `json:"season"`     //商品季节
	CreateTime int     `json:"createTime"` //商品创建时间
	Img        string  `json:"img"`        //商品预览图
	Note       string  `json:"note"`       //商品介绍
	Sum        int     `json:"sum"`        //库存总量
	Price      float32 `json:"price"`      //模糊价格
	OutGoods   int     `json:"out_goods"`  //出货量
	OnGoods    int     `json:"on_goods"`   //进货量
}

type ListReqBody struct {
	PageSize int               `json:"page_size"` //请求页大小
	PageNum  int               `json:"page_num"`  //请求页码
	Filter   ListReqBodyFilter `json:"filter"`    //过滤条件
	Sort     ListReqBodySort   `json:"sort"`      //排序方式
}

type ListReqBodyFilter struct {
	Season int    `json:"season"` //季节
	Search string `json:"search"` //搜索关键字
	Num    string `json:"num"`    //数量过滤 10-20 表示 10 ~ 20 库存的
	Time   string `json:"time"`   //按照时间过滤 毫秒级时间戳-毫秒级时间戳 表示一个时间区间
}

type ListReqBodySort struct {
	Key    string `json:"key"`    //排序方式 price-价格 num-库存 time-创建时间 out_goods-出货量 on_goods-进货量
	Method int    `json:"method"` //0为到升序 1为降序
}

func GetArticleList(data []byte, userId string) (*ListRes, error) {
	res := new(ListRes)
	req := new(ListReqBody)
	params := make([]any, 0)
	var Error = func(errMsg string) (*ListRes, error) {
		return res, errors.New(errMsg)
	}
	err := json.Unmarshal(data, req)
	if err != nil {
		return Error("request body type is not correct")
	} else {
		db, err := utools.GetDB()
		sql := NewListArticle(userId)
		if err != nil {
			return Error("DB connect error")
		} else {
			if req.PageSize < 1 || req.PageNum < 1 {
				return Error("capacity of not less than 1 page and page num logger of zero")
			} else {
				filter := req.Filter
				sort := req.Sort
				if filter.Season >= 1 && filter.Season <= 5 {
					sql.AddTerm("article_season")
					params = append(params, filter.Season)
				}
				if len(filter.Num) > 0 {
					numBase, err := utools.ParamsPassingNumber(filter.Num)
					if err != nil {
						return Error("filter.num is not true format")
					}
					sql.Custom("(sum BETWEEN ? AND ?)")
					params = append(params, numBase[0], numBase[1])
				}
				if len(filter.Search) > 0 {
					sql.Custom("article_name Like CONCAT('%',?,'%')")
					params = append(params, filter.Search)
				}
				if len(filter.Time) > 0 {
					numBase, err := utools.ParamsPassingNumber(filter.Time)
					if err != nil {
						return Error("filter.time is not true format")
					}
					sql.Custom("(create_time BETWEEN ? AND ?)")
					params = append(params, numBase[0], numBase[1])
				}
				if len(sort.Key) > 0 {
					key := ""
					method := ""
					switch sort.Key {
					case "time":
						key = "create_time"
					case "num":
						key = "sum"
					case "price":
						key = "price"
					case "out_goods":
						key = "out_goods"
					case "on_goods":
						key = "on_goods"
					default:
						return Error("sort.key is not true format")
					}
					if sort.Method == 1 {
						method = "DESC"
					} else {
						method = "ASC"
					}
					sql.Sort(key, method)
				}
				params = append(params, (req.PageNum-1)*req.PageSize, req.PageSize)
				row, err := db.Query(sql.Get(), params...)
				if err != nil {
					return Error(fmt.Sprint("DB ERR: ", err.Error()))
				} else {
					for row.Next() {
						newListResItem := ListResItem{}
						err := row.Scan(
							&newListResItem.Id,
							&newListResItem.Name,
							&newListResItem.Season,
							&newListResItem.CreateTime,
							&newListResItem.Img,
							&newListResItem.Note,
							&newListResItem.Sum,
							&newListResItem.Price,
							&res.Total,
							&newListResItem.OutGoods,
							&newListResItem.OnGoods)
						if err != nil {
							return Error(fmt.Sprint("DB input error: ", err.Error()))
						} else {
							res.List = append(res.List, newListResItem)
						}
					}
				}

				res.Page = req.PageNum
				res.Size = req.PageSize
				return res, nil
			}
		}
	}
}

func DropHouse(houseId string) error {
	if len(houseId) != 36 {
		return errors.New("id format not true")
	}
	db, err := utools.GetDB()
	if err != nil {
		return err
	} else {
		_, err := db.Exec(DropUserHouse, houseId)
		if err != nil {
			return err
		}
		return nil
	}
}

type DetailHouse struct {
	Id         string            `json:"id"`          //货物id
	Name       string            `json:"name"`        //货物名称
	Img        string            `json:"img"`         //货物预览图
	Season     int               `json:"season"`      //销售季节
	CreateTime int               `json:"create_time"` //创建时间 格式为毫秒级时间戳
	Note       string            `json:"note"`        //详情说明
	List       []DetailHouseList `json:"list"`        //选项列表
	Price      float32           `json:"price"`       //模糊价格
	Sales      int               `json:"sales"`       //销量
	Num        int               `json:"num"`         //库存总量
}
type DetailHouseList struct {
	Id   string                  `json:"id"`   //选项类id
	Name string                  `json:"name"` //选项类名
	Note string                  `json:"note"` //选项说明
	List []DetailHouseListDetail `json:"list"` //选项列表
}

type DetailHouseListDetail struct {
	Id    string  `json:"id"`    //选项id
	Name  string  `json:"name"`  //选项名称
	Img   string  `json:"img"`   //选项预览图
	Price float32 `json:"price"` //选项价格
}

func HouseDetailSql(houseId string) (DetailHouse, error) {
	var Error = func(errMsg string) (DetailHouse, error) {
		return DetailHouse{}, errors.New(errMsg)
	}
	if len(houseId) != 36 {
		return Error("id format not true")
	} else {
		db, err := utools.GetDB()
		if err != nil {
			return Error(err.Error())
		} else {
			row, err := db.Query(HouseDetail, houseId)
			if err != nil {
				return Error(err.Error())
			} else {
				res := DetailHouse{}
				var price float32 = 0
				var isValuation = true
				mapClass := map[string]*DetailHouseList{}
				for row.Next() {
					itemClass := DetailHouseList{}
					itemDetail := DetailHouseListDetail{}
					err := row.Scan(
						&res.Id,
						&res.Name,
						&res.Img,
						&res.Season,
						&res.CreateTime,
						&res.Note,
						&itemClass.Id,
						&itemClass.Name,
						&itemClass.Note,
						&itemDetail.Id,
						&itemDetail.Name,
						&itemDetail.Img,
						&itemDetail.Price)
					if err != nil {
						return Error(fmt.Sprint("DB input ERROR: ", err.Error()))
					}
					classMapItem, ok := mapClass[itemClass.Id]
					if ok {
						classMapItem.List = append(classMapItem.List, itemDetail)
					} else {
						itemClass.List = []DetailHouseListDetail{itemDetail}
						mapClass[itemClass.Id] = &itemClass
					}
				}
				for _, v := range mapClass {
					if len(v.List) > 0 && isValuation {
						isValuation = false
						price = v.List[0].Price
					}
					res.List = append(res.List, *v)
				}
				res.Price = price
				err := db.QueryRow(HouseNum, houseId).Scan(&res.Num)
				err = db.QueryRow(CountHouse, houseId).Scan(&res.Sales)
				if err != nil {
					return Error(fmt.Sprint("DB inout ERROR: ", err.Error()))
				}
				return res, nil
			}
		}
	}
}

type InventoryReq struct {
	Id        string  `json:"id"`         //商品的id
	Key       string  `json:"key"`        //本次记录的关键字
	OpenPrice float32 `json:"open_price"` //进价 默认为0
	SellPrice float32 `json:"sell_price"` //出价 默认为0
	Note      string  `json:"note"`       //备注
	Method    int     `json:"method"`     //0是出货 1是进货 默认是出货
	ClassKey  string  `json:"class_key"`  //选项id key1;key2;key3; 格式记录
	Num       int     `json:"num"`        //出货或者进货的量
}

func InventorySql(data []byte) error {
	req := new(InventoryReq)
	err := json.Unmarshal(data, req)
	if err != nil {
		return errors.New("request body type is not correct")
	} else {
		db, err := utools.GetDB()
		if err != nil {
			return errors.New(fmt.Sprint("db connect error: ", err.Error()))
		} else {
			if len(req.Id) == 0 {
				return errors.New("id format not true")
			}
			if len(req.ClassKey) == 0 {
				return errors.New("class_key at least one")
			}
			if len(req.Key) <= 0 {
				return errors.New("key format not true")
			}
			if req.Num <= 0 {
				return errors.New("num must be true")
			}
			if req.OpenPrice < 0 {
				return errors.New("open_price must be true")
			}
			if req.SellPrice < 0 {
				return errors.New("sell_price must be true")
			}
			if req.Method != 1 && req.Method != 0 {
				return errors.New("method is between 0 and 1")
			}
			_, err := db.Exec(AddInventory, utils.UUID(), req.Id, req.Key, req.OpenPrice, req.SellPrice, req.Note, req.Method, req.ClassKey, req.Num)
			if err != nil {
				return errors.New(fmt.Sprint("db input error: ", err.Error()))
			}
			return nil
		}
	}
}

func ClassificationInventorySql(classKey string) (int, error) {
	var Error = func(errMsg string) (int, error) {
		return 0, errors.New(errMsg)
	}
	if len(classKey) > 0 {
		db, err := utools.GetDB()
		if err != nil {
			return Error(fmt.Sprint("DB connect Error: ", err.Error()))
		} else {
			num := 0
			err := db.QueryRow(ClassificationInventory, classKey).Scan(&num)
			if err != nil {
				errMsg := err.Error()
				if errMsg == "sql: no rows in result set" {
					return num, nil
				}
				return Error(fmt.Sprint("DB input Error: ", err.Error()))
			} else {
				return num, nil
			}
		}
	} else {
		return Error("class_key at least one")
	}
}

type LoginReq struct {
	Name string `json:"name"` //用户名
	Pwd  string `json:"pwd"`  //密码
}

func UserLogin(data []byte) (string, error) {
	req := new(LoginReq)
	err := json.Unmarshal(data, req)
	var Error = func(errMsg ...any) (string, error) {
		return "", errors.New(fmt.Sprint(errMsg...))
	}
	if err != nil {
		return Error("request body type is not correct")
	} else {
		nameLen := len(req.Name)
		pwdLen := len(req.Pwd)
		if nameLen < 6 || nameLen > 18 {
			return Error("The name field must be between 6 to 18")
		}
		if pwdLen < 6 || pwdLen > 18 {
			return Error("The pwd field must be between 6 to 18")
		}
		db, err := utools.GetDB()
		if err != nil {
			return Error("DB connect Error: ", err.Error())
		} else {
			id := ""
			err = db.QueryRow(LoginUser, req.Name, req.Pwd).Scan(&id)
			if err != nil {
				if err.Error() == "sql: no rows in result set" {
					return Error("name or password Error")
				}
				return Error("DB input Error: ", err.Error())
			} else {
				return id, nil
			}
		}
	}
}
